package com.cari.oa.dao;

import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.hibernate.Query;
import org.springframework.stereotype.Repository;

import com.cari.oa.domain.MarketCostEntity;
import com.cari.oa.util.StrUtils;

@Repository
public class MarketCostDao extends BaseDao<MarketCostEntity> {

	
	@SuppressWarnings("unchecked")
	public List<MarketCostEntity> getPage(String applyUser, String selectProvince, Integer status, 
			Integer sort, Set<String> userProvinces, int page, int size){
		
		StringBuilder sb = new StringBuilder();
		sb.append("select m.* from T_MARKETCOST m ");
		sb.append("left join T_SYS_USER s on m.apply_user = s.enname ");
		sb.append("left join ");
		sb.append("(select  OBJECT_ID as cost_id, max(create_time) as create_time  from T_COMMENT where OBJECT_TYPE = 1 group by OBJECT_ID) c  on m.id = c.cost_id where 1=1 ");
		if(userProvinces != null && userProvinces.size() != 0) 
			sb.append(" and m.province in (:provinces)");
		
		if(status != null) 
			sb.append(" and m.status = :status");
		
		if(StrUtils.isNotEmpty(selectProvince))
			sb.append(" and m.province = :province");
		
		if(StrUtils.isNotEmpty(applyUser))
			sb.append(" and s.cnname like :applyUser");
		
		if(sort == null) sort = -1;
		if(sort == 1) sb.append(" order by c.create_time desc nulls last");
		else sb.append(" order by m.apply_time desc nulls last");
		
		Query query = getSession().createSQLQuery(sb.toString()).addEntity(MarketCostEntity.class);
		
		if(userProvinces != null && userProvinces.size() != 0) 
			query.setParameterList("provinces", userProvinces);
		
		if(status != null) 
			query.setParameter("status", status);
		
		if(StrUtils.isNotEmpty(selectProvince))
			query.setParameter("province", selectProvince);
		
		if(StrUtils.isNotEmpty(applyUser))
			query.setParameter("applyUser", "%" + applyUser + "%");
		
		
		query.setFirstResult((page-1)*size).setMaxResults(size);
		return query.list();
	}
	
	
	public long getCount(String applyUser, String selectProvince, Integer status, 
			Integer sort, Set<String> userProvinces){
		StringBuilder sb = new StringBuilder();
		sb.append("select count(*) from T_MARKETCOST m ");
		sb.append("left join T_SYS_USER s on m.apply_user = s.enname ");
		sb.append("left join ");
		sb.append("(select OBJECT_ID as cost_id, max(create_time) as create_time from T_COMMENT where OBJECT_TYPE = 1  group by OBJECT_ID) c  on m.id = c.cost_id where 1=1 ");
		if(userProvinces != null && userProvinces.size() != 0) 
			sb.append(" and m.province in (:provinces)");
		
		if(status != null) 
			sb.append(" and m.status = :status");
		
		if(StrUtils.isNotEmpty(selectProvince))
			sb.append(" and m.province = :province");
		
		if(StrUtils.isNotEmpty(applyUser))
			sb.append(" and s.cnname like :applyUser");
			
		if( sort == null) sort = -1;
		if(sort == 1) sb.append(" order by c.create_time desc nulls last");
		else sb.append(" order by m.apply_time desc nulls last");

		Query query = getSession().createSQLQuery(sb.toString());
		
		if(userProvinces != null && userProvinces.size() != 0) 
			query.setParameterList("provinces", userProvinces);
		
		if(status != null) 
			query.setParameter("status", status);
		
		if(StrUtils.isNotEmpty(selectProvince))
			query.setParameter("province", selectProvince);
		
		if(StrUtils.isNotEmpty(applyUser))
			query.setParameter("applyUser", "%" + applyUser + "%");
		Object object = query.uniqueResult();
		
		return Long.parseLong(object.toString());
	}
	
	public long getUserCount(String applyUser, String selectProvince, Integer status, 
			Integer sort, Set<String> userProvinces){
		StringBuilder hql = new StringBuilder();
		hql.append("select count(*) from t_marketcost m ");
		hql.append("left join (select OBJECT_ID as cost_id, max(create_time) as create_time  from T_COMMENT where OBJECT_TYPE = 1 group by OBJECT_ID) c on m.id = c.cost_id where 1=1 ");
		if(userProvinces != null && userProvinces.size() != 0) 
			hql.append(" and m.province in (:provinces)");
		
		if(status != null) 
			hql.append(" and m.status = :status");
		
		if(StrUtils.isNotEmpty(selectProvince))
			hql.append(" and m.province = :province");
		
		if(StrUtils.isNotEmpty(applyUser))
			hql.append(" and m.apply_user = :applyUser");
		
		if( sort == null) sort = -1;
		if(sort == 1) hql.append(" order by c.create_time desc nulls last");
		else hql.append(" order by m.apply_time desc nulls last");
			
		Query query = getSession().createSQLQuery(hql.toString());
		
		if(userProvinces != null && userProvinces.size() != 0) 
			query.setParameterList("provinces", userProvinces);
		
		if(status != null) 
			query.setParameter("status", status);
		
		if(StrUtils.isNotEmpty(selectProvince))
			query.setParameter("province", selectProvince);
		
		if(StrUtils.isNotEmpty(applyUser))
			query.setParameter("applyUser", applyUser);
		Object object = query.uniqueResult();
		
		return Long.parseLong(object.toString());
	}
	
	@SuppressWarnings("unchecked")
	public List<MarketCostEntity> getUserPage(String applyUser, String selectProvince, Integer status, 
			Integer sort, Set<String> userProvinces, int page, int size){
		StringBuilder hql = new StringBuilder();
		hql.append("select m.* from t_marketcost m ");
		hql.append("left join (select OBJECT_ID as cost_id, max(create_time) as create_time  from T_COMMENT where OBJECT_TYPE = 1 group by OBJECT_ID) c on m.id = c.cost_id where 1=1 ");

		if(userProvinces != null && userProvinces.size() != 0) 
			hql.append(" and m.province in (:provinces)");
		
		if(status != null) 
			hql.append(" and m.status = :status");
		
		if(StrUtils.isNotEmpty(selectProvince))
			hql.append(" and m.province = :province");
		
		if(StrUtils.isNotEmpty(applyUser))
			hql.append(" and m.apply_user = :applyUser");
		
		if( sort == null) sort = -1;
		if(sort == 1) hql.append(" order by c.create_time desc nulls last");
		else hql.append(" order by m.apply_time desc nulls last");
		
		Query query = getSession().createSQLQuery(hql.toString()).addEntity(MarketCostEntity.class);
		
		if(userProvinces != null && userProvinces.size() != 0) 
			query.setParameterList("provinces", userProvinces);
		
		if(status != null) 
			query.setParameter("status", status);
		
		if(StrUtils.isNotEmpty(selectProvince))
			query.setParameter("province", selectProvince);
		
		if(StrUtils.isNotEmpty(applyUser))
			query.setParameter("applyUser", applyUser);
		
		query.setFirstResult((page-1)*size).setMaxResults(size);
		return query.list();
	}
	
	/**
	 * 按时间查询省份的预计费用和实际费用总额
	 * @author -- Heyyroup
	 * @2013-3-12 下午4:18:39
	 * @param year
	 * @param month
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public List<Map> getTotalCost(Integer year, Integer month, Integer page, Integer size){
		
		Calendar startTime = Calendar.getInstance();
		if(month == null)
			month = 0;
		
		if(year == null){
			startTime.setTime(new Date());
			startTime.set(startTime.get(Calendar.YEAR), month, 0, 0, 0, 0);
		}
		else{
			startTime.set(year, month, 0, 0, 0, 0);
		}
			
		StringBuilder hql = new StringBuilder();
		hql.append("select new Map(m.province as province, sum(m.expectCost) as sum_expect, sum(m.actualCost) as sum_actual ) from MarketCostEntity m ");
		hql.append("where 1=1 and m.applyTime > :startTime and m.applyTime < :endTime ");
		hql.append("and m.marketstatus = 1 and m.regoinstatus = 1 group by m.province");
		
		Query query = getSession().createQuery(hql.toString());
		
		if(year == null){
			query.setParameter("startTime", startTime.getTime());
			startTime.add(Calendar.YEAR, 1);
			query.setParameter("endTime", startTime.getTime());
		}
		else{
			query.setParameter("startTime", startTime.getTime());
			if(month == 0)
				startTime.add(Calendar.YEAR, 1);
			else
				startTime.add(Calendar.MONTH, 1);
			query.setParameter("endTime", startTime.getTime());
		}
		
		query.setFirstResult((page-1)*size).setMaxResults(size);
		return query.list();
	}
	
	/**
	 * 查询记录的条数
	 * @author -- Heyyroup
	 * @2013-3-14 下午3:06:08
	 * @param year
	 * @param month
	 * @return
	 */
	public long getTotalCostCount(Integer year, Integer month){
		Calendar startTime = Calendar.getInstance();
		if(month == null)
			month = 0;
		
		if(year == null){
			startTime.setTime(new Date());
			startTime.set(startTime.get(Calendar.YEAR), month, 0, 0, 0, 0);
		}
		else{
			startTime.set(year, month, 0, 0, 0, 0);
		}
			
		StringBuilder hql = new StringBuilder();
		hql.append("select count( distinct m.province ) from MarketCostEntity m ");
		hql.append("where 1=1 and m.applyTime > :startTime and m.applyTime < :endTime ");
		hql.append("and m.marketstatus = 1 and m.regoinstatus = 1");
		
		Query query = getSession().createQuery(hql.toString());
		
		if(year == null){
			query.setParameter("startTime", startTime.getTime());
			startTime.add(Calendar.YEAR, 1);
			query.setParameter("endTime", startTime.getTime());
		}
		else{
			query.setParameter("startTime", startTime.getTime());
			if(month == 0)
				startTime.add(Calendar.YEAR, 1);
			else
				startTime.add(Calendar.MONTH, 1);
			query.setParameter("endTime", startTime.getTime());
		}
		
		return (Long) query.uniqueResult();
	}
	
	/**
	 * 查询某个省份的实际费用总额和预期费用总额
	 * @author -- Heyyroup
	 * @2013-3-14 下午3:06:33
	 * @param province
	 * @param year
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public List<Map> getTotalCostByProvince(String province, Integer year){
		Calendar startTime = Calendar.getInstance();
		
		if(year == null){
			startTime.setTime(new Date()); 
			startTime.set(startTime.get(Calendar.YEAR), 0, 0, 0, 0, 0);
		}
		else
			startTime.set(year, 0, 0, 0, 0, 0);
		
		StringBuilder hql = new StringBuilder();
		hql.append("select new Map(MONTH(m.applyTime) as re_month, sum(m.expectCost) as sum_expect, sum(m.actualCost) as sum_actual) from MarketCostEntity m where 1=1 ");
		hql.append(" and m.province = :province ");
		hql.append(" and m.marketstatus = 1 and m.regoinstatus = 1 ");
		
		hql.append(" and m.applyTime > :startTime ");
		hql.append(" group by MONTH(m.applyTime)");
		hql.append(" order by MONTH(m.applyTime)");
		
		Query query = getSession().createQuery(hql.toString());
		
		if(StrUtils.isNotEmpty(province))
			query.setParameter("province", province);
		else
			query.setParameter("province", "");
		query.setParameter("startTime", startTime.getTime());
		return query.list();
	}
	
	@SuppressWarnings("unchecked")
	public List<Integer> getAllRecordsYear(){
		String hql = "select distinct(YEAR(m.applyTime)) from MarketCostEntity m";
		
		Query query = getSession().createQuery(hql);
		
		return query.list();
	}
}
